"""
    Author:
    Date: 2018-10-30

    DB访问辅助类。
"""

import re
import redis
import time
import MySQLdb

from MySQLdb.cursors import DictCursor
from DBUtils.PooledDB import PooledDB
from django.conf import settings

from utils.log import Log

# mysql连接池
g_mysql_pool = None


# ------------------------------------------------------------------------------
# MYSQL
# ------------------------------------------------------------------------------

def fetch_mysql_connection_from_pool(db_config):
    """ 从mysql的缓存池中获取mysql链接 """
    global g_mysql_pool

    host = db_config.get('host', '127.0.0.1')
    port = db_config.get('port', 3306)
    charset = db_config.get('charset', 'utf8mb4')
    user = db_config['user']
    passwd = db_config['password']
    db = db_config['database']

    if ':' in host:
        host, port = host.split(':')
        host = host.strip()
        port = int(port.strip())

    try:
        if g_mysql_pool is None:
            g_mysql_pool = PooledDB(
                creator=MySQLdb,
                mincached=1,
                maxcached=10,
                host=host,
                port=port,
                user=user,
                passwd=passwd,
                db=db,
                use_unicode=True,
                charset=charset,
                cursorclass=DictCursor)
        return g_mysql_pool.connection()
    except (MySQLdb.Error, MySQLdb.Warning) as e:
        Log.error(f'Fetch mysql connection from pool failed: {e}')
        return None


def query_data_from_db(sql, params=None, db=settings.DB_CONFIG):
    """ 从数据库中提取数据 """
    # 从数据库中查找数据
    query_data = []
    conn = None
    cursor = None

    try:
        try:
            conn = fetch_mysql_connection_from_pool(db)
            if conn is None:
                return None

            start_time = time.time()
            cursor = conn.cursor()
            if params is None:
                cursor.execute(sql)
            else:
                cursor.execute(sql, params)

            index = 0
            while True:
                res = cursor.fetchone()
                if not res:
                    break
                record = {}
                index += 1
                record['index_'] = index
                for key, val in res.items():
                    record[key] = val
                query_data.append(record)
            cursor.close()
            conn.commit()
            sql_execute_time = time.time() - start_time
            if sql_execute_time > 0.1:
                print('===> %f: %s' % (sql_execute_time, re.sub(' +', ' ', sql.replace('\n', ''))))
        except (MySQLdb.Error, MySQLdb.Warning) as e:
            Log.error(f'Mysql exception: error={e}, sql={sql}, params={params}')
            return None
    finally:
        if cursor is not None:
            cursor.close()

        if conn is not None:
            conn.rollback()
    return query_data


def execute_sql(sql, params=None, db=settings.DB_CONFIG):
    """ 执行sql语句, 返回影响的行数"""
    conn = None
    cursor = None
    affected_rows = 0

    try:
        try:
            conn = fetch_mysql_connection_from_pool(db)
            if conn is None:
                return 0

            start_time = time.time()
            cursor = conn.cursor()
            if params is None:
                cursor.execute(sql)
            else:
                cursor.execute(sql, params)

            # affected_rows = conn.affected_rows()
            affected_rows = cursor.rowcount
            cursor.close()
            conn.commit()
            sql_execute_time = time.time() - start_time
            if sql_execute_time > 0.1:
                sql = sql.replace('\n', '')
                sql = re.sub(' +', ' ', sql)
                Log.error(f'Check sql for cost more than 1 second: run_time={sql_execute_time}, sql={sql}, params={params}')
        except (MySQLdb.Error, MySQLdb.Warning) as e:
            Log.error(f'Mysql exception: error={e}, sql={sql}, params={params}')
            return 0
    finally:
        if cursor is not None:
            cursor.close()
        if conn is not None:
            conn.rollback()
    return affected_rows


# ------------------------------------------------------------------------------
# REDIS
# ------------------------------------------------------------------------------

def get_redis(db=0):
    """ 获取redis链接 """
    host = settings.REDIS_CONFIG['host']
    port = settings.REDIS_CONFIG['port']
    password = settings.REDIS_CONFIG['password']
    r = redis.Redis(host=host, port=port, db=db, password=password)
    return r


def redis_get(key, db=0):
    r = get_redis(db)
    result = r.get(key)
    if type(result) is bytes:
        return result.decode('utf-8')
    else:
        return result


def redis_set(key, value, ex=None, db=0):
    r = get_redis(db)
    r.set(key, value, ex)


def redis_sadd(key, member, db=0):
    r = get_redis(db)
    r.sadd(key, member)


def redis_srem(key, member, db=0):
    r = get_redis(db)
    r.srem(key, member)


def redis_sismember(key, member, db=0):
    r = get_redis(db)
    return r.sismember(key, member)


def redis_smembers(key, db=0):
    r = get_redis(db)
    return r.smembers(key)


def redis_zadd(key, score, member, db=0):
    r = get_redis(db)
    # ！！注意：zadd的参数和redis不同，member在score前面
    r.zadd(key, member, score)


def redis_zrem(key, member, db=0):
    r = get_redis(db)
    r.zadd(key, member)


def redis_zrevrange(key, start, stop, db=0, withscores=True):
    r = get_redis(db)
    return r.zrevrange(name=key, start=start, end=stop, withscores=withscores)


def redis_hset(name, key, value, db=0):
    r = get_redis(db)
    return r.hset(name, key, value)


def redis_expire(key, seconds, db=0):
    r = get_redis(db)
    r.expire(key, seconds)


# ------------------------------------------------------------------------------
if __name__ == '__main__':
    print('ok')
